import xlrd
import os
import datetime
from MysqlSet import MysqlDb
sql_connect = MysqlDb()


path = u"D:/srwork/sql/data0427/"



def insert_data():
    dir_list = os.listdir(path)

    for dir in dir_list:
        file_list = os.listdir(path +"/"+dir)
        for file in file_list:
            print "{dir}/{file_name}".format(dir = dir,file_name = file)
            try:
                sql =compile_file_content(dir,file)
                sql_connect.open()
                sql_connect._execute_sql(sql)
            except:
                print "error : {dir}/{file_name}".format(dir = dir,file_name = file)

def reinsert_data():
    dir = "error_info"
    file_list = os.listdir(path+"/"+dir)
    for file in file_list:
        print "{dir}/{file_name}".format(dir = dir,file_name = file)
        try:
            sql =compile_file_content(dir,file)
            sql_connect.open()
            sql_connect._execute_sql(sql)
        except Exception,e:
            print e
            print "error : {dir}/{file_name}".format(dir = dir,file_name = file)

def compile_file_content(dir,file_name):
    data = xlrd.open_workbook(path + dir +"/"+file_name)
    table = data.sheets()[0]
    nrows = table.nrows
    ncols = table.ncols
    table_head = get_table_head(table.name)
    data_source = []
    for i in range(1,nrows):
        row_data = []
        j = 0
        for j in range(1, ncols):
            if i == 0:
                pass
            else:
                try:
                    if j == ncols - 1:
                        updatetime = xlrd.xldate.xldate_as_datetime(table.cell(i, ncols - 1).value, 0)
                        row_data.append(str(updatetime))
                    elif j == 1:
                        row_data.append(str(int(table.cell(i, j).value)))
                    else:
                        row_data.append(str(table.cell(i, j).value))
                except Exception,e:
                    print e,i,j
                    break
        if i!= 0 and row_data != []:
            row_data = "('" + "','".join(row_data)+"')"
            data_source.append(row_data)
    try:
        end_time = datetime.datetime(updatetime.year,updatetime.month+1,1,0,0,0)
        sql = "delete from {table_name} where " \
              "updatetime > '{start_time}' and updatetime < '{end_time}';".format(table_name = table.name,
                                                                             start_time = datetime.datetime.strftime(updatetime,"%Y-%m"),
                                                                             end_time = datetime.datetime.strftime(end_time,"%Y-%m"))
    except Exception,e:
        sql = ''

    sql = "REPLACE INTO {table_name} ({col_name}) VALUES {value_list}".format(table_name = table.name,
                                                                           col_name = ",".join(table_head),
                                                                           value_list = ",".join(data_source))
    return sql



def get_table_head(table_name):
    table_prop = {
    "lamp_info_log": ['lampid', 'lighteness', 'lampstatus', 'lampvoltage', 'lampcurrent', 'lamppower', 'temper',
                      'updatetime'],
    "history_info_log": ['lampid', 'rundays', 'overtimes', 'fulltimes', 'totalchargeah', 'totaldischarah',
                         'updatetime'],
    "electric_info_log": ['lampid', 'daygeneration', 'dayconsumption', 'totalgeneration', 'totalconsumption',
                          'updatetime'],
    "battery_info_log": ['lampid', 'battstatus', 'chargestage', 'battvoltage', 'chargecurrent', 'discharcurrent',
                         'chargepower', 'dischargepower', 'batttemper', 'electrictotal', 'electricleft', 'electricSOC',
                         'voltagedaymin', 'voltagedaymax', 'daychargeah', 'daydischarah', 'daychargemaxpow',
                         'daydischarmaxpow', 'daychargemincurrent', 'daycharmaxcurrent', 'daydischargemincurrent',
                         'daydischarmaxcurrent', 'daybattmintemper', 'daybattmaxtemper', 'updatetime'],
    "alarm_info_log": ['lampid', 'alarmtype', 'stralarmtype', 'status', 'updatetime'],
    "system_info_log": ['lampid', 'sysvoltage', 'syscurrent', 'temper', 'updatetime'],
    "solar_info_log": ['lampid', 'solarvoltage', 'solarcurrent', 'solarpower', 'updatetime'],
    }
    return table_prop.get(table_name,[])

if __name__ == '__main__':
    insert_data()